Accessing data in the web

Application Programming Interfaces (APIs)

• Definition: Structured gateways allowing to query databases and retrieve data automatically.
• Examples:
  • FRED API
  • World Bank Data API
  • IMF API

There tends to be thorough documentation on how to access the API. A Key is typically required.

FRED API

Some data sources have python libraries. fredapi is a good example and it is installed with conda install fredapi

import pandas as pd
import matplotlib as plt
from fredapi import Fred
fred = Fred(api_key='6082258bcc5fa73a032d9d60c890f744')
data = fred.get_series('SP500')
data = pd.DataFrame(data)
data.plot()

Multiple queries

import requests
import pandas as pd
series = ["SP500", "DJIA", "NASDAQCOM"]
api_key = "6082258bcc5fa73a032d9d60c890f744"

df = pd.DataFrame()
for serie in series :

    url = f"https://api.stlouisfed.org/fred/series/observations?series_id={serie}&api_key={api_key}&file_type=json"

    response = requests.get(url)
    response = response.json()

    dfi = pd.DataFrame(response['observations'])
    dfi = dfi[['value', 'date']]
    
    dfi.set_index('date', inplace=True)
    dfi.index = pd.to_datetime(dfi.index)

    dfi.rename(columns = { "value" : f"fred_{serie}" }, inplace=True)
    dfi[f"fred_{serie}"] = dfi[f"fred_{serie}"].apply(pd.to_numeric, errors='coerce')
    
    df = df.merge(dfi, left_index=True, right_index=True, how='outer')
    df = pd.DataFrame(df.resample("D").mean())
    df.sort_index(inplace=True)
  
df.tail(5)

Direct web requests

When no API is available, data can still be retrieved from static web resources. Files are downloaded using HTTP requests.

Formats: .csv, .xlsx, .txt, .zip, .pdf

pandas can sometimes manage this directly but sometimes you need a request

#| echo: false
import pandas as pd
url = "https://example.com/data.csv"
df = pd.read_csv(url)

#| echo: false
import requests
import pandas as pd
from io import StringIO

# Step 1: Download the CSV file via HTTP request
url = "https://example.com/data.csv"
response = requests.get(url)

# Step 2: Check that the request was successful
if response.status_code == 200:
    # Step 3: Convert content into DataFrame
    csv_data = StringIO(response.text)
    df = pd.read_csv(csv_data)
    
    print("Download complete. Data preview:")
else:
    print(f"Failed to download file. Status code: {response.status_code}")

Advanced Requests and Headers

Most websites block requests that do not come from explorers. We can use request options to send header information.

hdr = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537'}

url = "https://www.bahamas.gov.bs"
r = requests.get(url=url, verify=None, headers=hdr).content

headers: the website is notified that the request is made through Mozilla/Chrome

verify: SSL certificate verification

Request output with Beautiful Soup

Most websites are developed in HTML. Beautiful Soup is a web parser that readers HTML tags.

hdr = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537'}

url = "https://www.bahamas.gov.bs"
r = requests.get(url=url, verify=None, headers=hdr).content
soup = BeautifulSoup(r)

The website is now stored on the variable soup.

We can list all html <a> tags (links) using:

links = [ link.get("href") for link in soup('a') if link.get("href") is not None ]

Opening the file

We can use the trick above to list all .csv or .xlsx files in a website:

hdr = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537'}

url = "https://www.bahamas.gov.bs"
r = requests.get(url=url, verify=None, headers=hdr).content
soup = BeautifulSoup(r)
links = [ link.get("href") for link in soup('a') if link.get("href") is not None and "csv" in link.get('href') ]

The list links will have the URL of all csv files. Now we can open the file:

get = links[0]
response = requests.get(get, verify=None, headers=hdr)
df = pd.read_csv(response.content)

Downloading

Suppose we have stored a file’s url in the variable “get”. Many times we can open this file directly:

response = requests.get(get, stream=True)

Sometimes, however, we need to download the file:

response = requests.get( get , verify=False, timeout=300, headers=hdr)
file_name = "MyFile.pdf"
file_name = f"{folder_spec}/{file_name}"
with open( file_name , 'wb') as file: file.write(response.content)
print(f"Downloaded")

Managing pdfs

pdfs are the most complicated data structures to clean.

PyPDF2 reads the pdf and eases finding the right page.

import tabula, camelot, PyPDF2

def extract_page_text(pdf_path):
  '''
  extract_page_text function opens a pdf an reads all the text. It is used to search specific pages
  '''  
  def extract_page_text(pdf_path):
    with open(pdf_path, 'rb') as pdf_file:
        reader = PyPDF2.PdfReader(pdf_file)
        page_texts = [page.extract_text() for page in reader.pages]
    return page_texts

camelot and tabula specialize in managing pdf Tables.

The pdf process

import tabula, camelot, PyPDF2, requests
import pandas as pd

response = requests.get(get, stream=True)

file_name = f"{folder_spec}/gdp_file.pdf"
with open( file_name , "wb") as pdf_file:
    for chunk in response.iter_content(chunk_size=8192):
        if chunk:
            pdf_file.write(chunk)

# Find the page number
page_texts = extract_page_text(file_name)
table_2_pages = [i + 1 for i, text in enumerate(page_texts) if "TABLE 2" in text ]

# Iterate over all pages with the word TABLE 2 and extract them as tables inside a list "tables"
if table_2_pages :
    tables = []
    for page in table_2_pages:
        page_tables = camelot.read_pdf(file_name, pages=str(page))
        tables.extend(page_tables)

  df0 = pd.DataFrame()
  for x in range(0,len(tables)) :
      # Open the Table
      df = tables[x].df
      # Cleaning

      df0 = pd.concat([df0, df], axis=0)

The pdf process: step-by-step

Opening the file

import camelot, PyPDF2
import pandas as pd

def extract_page_text(pdf_path):
  with open(pdf_path, 'rb') as pdf_file:
      reader = PyPDF2.PdfReader(pdf_file)
      page_texts = [page.extract_text() for page in reader.pages]
  return page_texts

file_name = f"session_2_files/gdp_file.pdf"

# Find the page number
page_texts = extract_page_text(file_name)
table_2_pages = [i + 1 for i, text in enumerate(page_texts) if "TABLE 2" in text ]
print(table_2_pages)

[6, 7, 8]

Working dataset

National Budget 10 Year Monthly Data (xlsx)

Open the file with pandas

import pandas as pd

file = "session_2_files/MonthlyData.xlsx"
df = pd.read_excel(file)
df

Clean up column names

df.columns = df.columns.str.normalize('NFKD').str.encode('ascii', errors='ignore').str.decode('utf-8').str.lower()
for col in df.columns :
    if "unnamed: 0" in col : df.rename(columns = {col : 'variable'}, inplace=True)
df.variable = df.variable.str.normalize('NFKD').str.encode('ascii', errors='ignore').str.decode('utf-8').str.lower().str.strip()
df = df.dropna(subset='variable')
df

Select variables

df = df[
    df['variable'].str.startswith("tax revenue")
]
# Add fiscal to all variable names
df['variable'] = 'fiscal ' + df.variable

# clean variable name
df['variable'] = (
    df['variable']
    .str.replace(r"\s*\(.*?\)", "", regex=True)   # remove parentheses + contents
    .str.replace(r"\s+", " ", regex=True)         # collapse multiple spaces
    .str.strip()                                  # trim
    .str.replace(r" ", "_")                       # Change spaces to _
)
df

Reshape in action

import numpy as np
df = pd.melt(df, id_vars="variable", var_name="date", value_name='value')
df.date = pd.to_datetime(df.date, infer_datetime_format=True, errors='coerce')
df.set_index('date', inplace=True)
df.loc[df['value']==0, 'value'] = np.nan
df = df.dropna()
df

Pivot and Save

If we had multiple variables, we would rather have them as columns.

df = df.pivot(columns='variable', values='value')
df.to_csv(f"session_2_files/clean_file.csv")
df

Let’s visualize

import matplotlib as plt
df.plot()

Nighttime Lights

Nighttime satellite imagery provides a valuable proxy for economic activity, electrification, and urban growth — particularly in areas with limited or unreliable statistical data.

It measures the intensity of light emissions captured by satellites orbiting the Earth.
Data are typically available at fine spatial and temporal resolutions, making it a powerful complement to conventional economic indicators.

Why It’s Complex

Working with satellite data —especially NASA’s Nighttime Lights (VIIRS) data— involves multiple steps and tools.

• This is one of the most complex code blocks in our workflow, combining geospatial processing, data scraping, and time-series construction.

Camelot

Running camelot may need some additional Libraries

conda install -c conda-forge cv2 --yes pip install opencv-contrib-python